﻿using System;
using Excel;
using System.IO;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using WeifenLuo.WinFormsUI.Base;

namespace HRM
{
    public class CreateExcelDoc
    {
        private Application app = null;
        private Workbook workbook = null;
        private Worksheet worksheet = null;
        private Range workSheet_range = null;

        public CreateExcelDoc()
        {
            createDoc();
        }

        public void createDoc()
        {
            try
            {
                app = new Application();
                //app.Visible = true;
                workbook = app.Workbooks.Add(1);
                worksheet = (Worksheet)workbook.Sheets[1];
            }
            catch (Exception ex)
            {
                Ctrl_LOG.Print("In CreateExcelDoc class", "At createDoc method.", "Error message: " + ex.Message);
            }
            finally
            {

            }
        }

        private bool createHeaders(int rowBegin, int colbegin, System.Windows.Forms.DataGridView gdv)
        {
            Range rangCell = null;
            foreach(System.Windows.Forms.DataGridViewColumn col in gdv.Columns)
            {
                if (!col.Visible) continue; // if column in grid is not visible --> didn't create column in excel sheet
                
                if(col.Name.Substring(0,1).Equals("d"))
                    worksheet.Cells[rowBegin, colbegin] = col.HeaderText.Substring(0, 3) + " " + col.Name.Replace("d", "(") + ")";
                else
                    worksheet.Cells[rowBegin, colbegin] = col.HeaderText;

                rangCell = worksheet.Cells[rowBegin, colbegin];
                rangCell.ColumnWidth = col.Width/10;
                rangCell.HorizontalAlignment = System.Windows.Forms.HorizontalAlignment.Center;
                rangCell.VerticalAlignment = System.Windows.Forms.HorizontalAlignment.Center;
                switch (col.HeaderCell.Style.BackColor.Name)
                {
                    case "Pink":
                    case "Red":
                        rangCell.Interior.Color = col.HeaderCell.Style.BackColor;
                        rangCell.Font.Color = System.Drawing.Color.White;
                        break;
                }
                colbegin++;
            }
            workSheet_range = worksheet.UsedRange; //get_Range("A" + rowBegin.ToString(), "AH" + rowBegin.ToString());
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.Font.Bold = true;
            workSheet_range.WrapText = true;
            return true;
        }

        private bool addData(int rowBegin, int colBegin, System.Windows.Forms.DataGridView dgv)
        {
            if (dgv.Rows.Count <= 0)
            {
                if(app != null) app.Visible = true;
                return true;
            }

            DataSet lds = (DataSet)dgv.DataSource;
            if (lds == null || lds.Tables == null || lds.Tables[0].Rows.Count <= 0)
            {
                if(app != null) app.Visible = true;
                return true; 
            }

            foreach (DataRow row in lds.Tables[0].Rows)
            {
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    if (!dgv.Columns[i].Visible) continue;

                    if (row[i].GetType().ToString().Equals("System.DateTime"))
                        worksheet.Cells[rowBegin, colBegin].EntireColumn.NumberFormat = "dd/MM/yyyy";
                    worksheet.Cells[rowBegin, colBegin] = row[i].ToString().Trim();
                    row[i].GetType();
                    worksheet.Cells[rowBegin, colBegin].Borders.Color = System.Drawing.Color.Black.ToArgb();
                    colBegin++;
                }
                rowBegin++;
                colBegin = 1;
            }
            //worksheet.Cells[row, col] = data;
            //workSheet_range = worksheet.get_Range(cell1, cell2);
            //workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            //workSheet_range.NumberFormat = format;
            app.Visible = true;
            return true;
        }

        public string ExportDGV2XLSX(int rowBegin, int colBegin, System.Windows.Forms.DataGridView dgv)
        {
            try
            {
                //creates text header for excel file
                if (createHeaders(rowBegin, colBegin, dgv))
                {
                    // Add data to excel file
                    addData(rowBegin + 1, colBegin, dgv);
                }
                return "";
            }
            catch (Exception ex)
            {
                Ctrl_LOG.Print("In CreateExcelDoc class", "At ExportDGV2XLSX method.", "Error message: " + ex.Message);
                if (app != null)
                { 
                    workbook = null;
                    worksheet = null;
                    workSheet_range = null;
                    app.Workbooks.Close();
                    app.Quit();
                }
                return ex.Message;
            }        
        }

        public DataSet Xlsx2DsByDataReader(string asFilePath)
        {
            IExcelDataReader excelReader = null;
            app = new Application();
            string fileExtension = Path.GetExtension(asFilePath);

            FileStream stream = File.Open(asFilePath, FileMode.Open, FileAccess.Read);
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            if(fileExtension.Equals(".xls"))
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            else if(fileExtension.Equals(".xlsx"))
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            excelReader.Close();

            return result;
        }

        private void TestExcel(string asFilePath)
        {
            app = new Application();
            
            try
            {
                app.Visible = false;
                app.ScreenUpdating = false;
                app.DisplayAlerts = false;

                workbook = app.Workbooks.Open(asFilePath, Missing.Value, Missing.Value, Missing.Value
                                                  , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                 , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                                , Missing.Value, Missing.Value, Missing.Value);

                foreach (Worksheet sheet in workbook.Worksheets)
                {

                    Console.WriteLine(@"Values for Sheet " + sheet.Index);

                    // get a range to work with
                    workSheet_range = sheet.get_Range("A1", Missing.Value);
                    // get the end of values to the right (will stop at the first empty cell)
                    workSheet_range = workSheet_range.get_End(XlDirection.xlToRight);
                    // get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
                    workSheet_range = workSheet_range.get_End(XlDirection.xlDown);

                    // get the address of the bottom, right cell
                    string downAddress = workSheet_range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                    // Get the range, then values from a1
                    workSheet_range = sheet.get_Range("A1", downAddress);
                    object[,] values = (object[,])workSheet_range.Value2;

                    // View the values
                    Console.Write("\t");
                    Console.WriteLine();
                    for (int i = 1; i <= values.GetLength(0); i++)
                    {
                        for (int j = 1; j <= values.GetLength(1); j++)
                        {
                            Console.Write("{0}\t", values[i, j]);
                        }
                        Console.WriteLine();
                    }

                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                workSheet_range = null;
                if (workbook != null)
                    workbook.Close(false, Missing.Value, Missing.Value);
                workbook = null;
                if (app != null)
                    app.Quit();
                app = null;
            }
        }


        public void ImportXLSX2DGV(String aFilePath, System.Windows.Forms.DataGridView adgv, Dictionary<string, object> aDict)
        {
            try
            {
                DataSet lds = (DataSet)adgv.DataSource;
                if (app == null) app = new Application();
                worksheet = app.Workbooks.Open(aFilePath).ActiveSheet;

                int index = 0;
                object rowIndex = 2;

                DataRow row;
                while (((Range)worksheet.Cells[rowIndex, 1]).Value2 != null)
                {
                    rowIndex = 2 + index;
                    row = lds.Tables[0].NewRow();
                    int i = 1;
                    foreach (DataColumn col in lds.Tables[0].Columns)
                    {
                        if (aDict.ContainsKey(col.ColumnName))
                            row[col.ColumnName] = aDict[col.ColumnName];
                        else
                        {
                            //if (((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing).GetType().ToString().Equals("System.DateTime"))
                            //{
                            //    object obj = ((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing);
                            //}
                            //string ls = row[col.ColumnName].GetType().ToString();
                            row[col.ColumnName] = ((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing);// Value2;
                            i++;
                        }
                    }
                    index++;
                    if (!row["ID_Emp"].ToString().Equals(""))
                        lds.Tables[0].Rows.Add(row);
                }
                worksheet = null;
                app.Workbooks.Close();
                app.Quit();
            }
            catch (Exception ex)
            {
                Ctrl_LOG.Print("In CreateExcelDoc class", "At Import method.", "Error message: " + ex.Message);
            }
            finally
            {
                if (app != null)
                {
                    if(app.Workbooks != null) app.Workbooks.Close();
                    app.Quit();
                }
            }
        }

        public void ImportXLSX2DGV(String aFilePath, System.Windows.Forms.DataGridView adgv, Dictionary<string, object> aDictDefaultVal, Dictionary<string, string> aDeictType)
        {
            try
            {
                DataSet lds = (DataSet)adgv.DataSource;
                if (app == null) app = new Application();
                worksheet = app.Workbooks.Open(aFilePath).ActiveSheet;

                int index = 0;
                object rowIndex = 2;

                DataRow row;
                while (((Range)worksheet.Cells[rowIndex, 1]).Value2 != null)
                {
                    rowIndex = 2 + index;
                    row = lds.Tables[0].NewRow();
                    int i = 1;
                    foreach (DataColumn col in lds.Tables[0].Columns)
                    {
                        if (aDictDefaultVal.ContainsKey(col.ColumnName))
                            row[col.ColumnName] = aDictDefaultVal[col.ColumnName];
                        else
                        {
                            if (aDeictType.ContainsKey(col.ColumnName))
                            {
                                switch (aDeictType[col.ColumnName])
                                {
                                    case "System.Decimal":
                                        object obj = ((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing);
                                        if (obj == null)
                                            row[col.ColumnName] = 0;
                                        else
                                            row[col.ColumnName] = obj; //((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing);
                                        i++;
                                        break;
                                    default:
                                        row[col.ColumnName] = ((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing);
                                        i++;
                                        break;
                                }
                            }
                            else
                            {
                                row[col.ColumnName] = ((Range)worksheet.Cells[rowIndex, i]).get_Value(Type.Missing);
                                i++;
                            }
                        }
                    }
                    index++;
                    if (!row["ID_Emp"].ToString().Equals(""))
                        lds.Tables[0].Rows.Add(row);
                }
                worksheet = null;
                app.Workbooks.Close();
                app.Quit();
            }
            catch (Exception ex)
            {
                Ctrl_LOG.Print("In CreateExcelDoc class", "At Import method.", "Error message: " + ex.Message);
            }
            finally
            {
                if (app != null)
                {
                    if (app.Workbooks != null) app.Workbooks.Close();
                    app.Quit();
                }
            }
        }

    }
}
